package com.geping.etl.common.repository;

import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.geping.etl.common.entity.Sys_UserAndOrgDepartment;

public interface RoleDistributionRepository extends CrudRepository<Sys_UserAndOrgDepartment,Integer>{
	
	//根据机构编号查询属于该机构下的用户信息(用户信息属于三表联查)
	@Query(value="select su.ID as id,su.LOGIN_ID as loginId,su.IS_DEPT as isDept,su.USER_ENAME as userEname,su.USER_CNAME as userCname,su.PASSWORD as password,su.ORG_ID as orgId,su.DEPART_ID as departId,su.TEL as tel,su.MOBILE as mobile,su.ADDRESS as address,su.EMAIL as email,su.IS_LOCKED as isLocked,su.USER_LOCKED_RESON as userLockedReson,su.START_DATE as startDate,su.END_DATE as endDate,su.SYSTEM_DEPT as systemDept,su.CREATE_TIME as createTime,su.DESCRIPTION as description,su.ENABLED as enabled,su.IS_DELETE as isDelete,su.LAST_LOGIN_DATE as lastLoginDate,su.LAST_MODIFY_DATE as lastModifyDate,su.IS_FIRST_LOGIN as isFirstLogin,su.HANDLENAME as handlename,su.HANDLEPERSON as handleperson,su.HANDLEDATE as handledate,su.IP as ip,sd.DEPARTMENT_NAME as departmentName,so.ORG_NAME as orgName,so.ORG_INSIDE_CODE as orgInsideCode,so.LICENSE_NUMBER as licenseNumber"
		                 +" from sys_user su"
		                 +" LEFT JOIN sys_department sd ON su.DEPART_ID = sd.DEPARTMENT_ID"
		                 +" LEFT JOIN sys_org so ON su.ORG_ID = so.ORG_ID"
		                 +" WHERE su.IS_DELETE = 'N' AND su.ORG_ID=?1"
		                 +" ORDER BY su.ID",nativeQuery=true)
	public List<Sys_UserAndOrgDepartment> findUserOrgDepartmentByOrgId(String orgid);

	@Query(value="select su.ID as id,su.LOGIN_ID as loginId,su.IS_DEPT as isDept,su.USER_ENAME as userEname,su.USER_CNAME as userCname,su.PASSWORD as password,su.ORG_ID as orgId,su.DEPART_ID as departId,su.TEL as tel,su.MOBILE as mobile,su.ADDRESS as address,su.EMAIL as email,su.IS_LOCKED as isLocked,su.USER_LOCKED_RESON as userLockedReson,su.START_DATE as startDate,su.END_DATE as endDate,su.SYSTEM_DEPT as systemDept,su.CREATE_TIME as createTime,su.DESCRIPTION as description,su.ENABLED as enabled,su.IS_DELETE as isDelete,su.LAST_LOGIN_DATE as lastLoginDate,su.LAST_MODIFY_DATE as lastModifyDate,su.IS_FIRST_LOGIN as isFirstLogin,su.HANDLENAME as handlename,su.HANDLEPERSON as handleperson,su.HANDLEDATE as handledate,su.IP as ip,sd.DEPARTMENT_NAME as departmentName,so.ORG_NAME as orgName,so.ORG_INSIDE_CODE as orgInsideCode,so.LICENSE_NUMBER as licenseNumber"
			+" from sys_user su"
			+" LEFT JOIN sys_department sd ON su.DEPART_ID = sd.DEPARTMENT_ID"
			+" LEFT JOIN sys_org so ON su.ORG_ID = so.ORG_ID"
			+" WHERE su.IS_DELETE = 'N' AND su.END_DATE like %?1%"
			+" ORDER BY su.ID",nativeQuery=true)
	List<Sys_UserAndOrgDepartment> findUserOrgDepartmentLikeOrgId(String orgid);

}
